# -*- coding: utf-8 -*-

from utils.DBService import sqlquery
import json
import os,datetime
from utils.DataUitl import getpar,getfrs

DIBANG_SQL = """select a.ID,a.departname,a.parentdepartid,b.gprsbianhao,b.banhezhanminchen 
from t_s_depart as a, WZDibangxinxi as b
where a.ID=b.departid and a.ID in ('{}')"""

CAILIAO_SQL = """select c.departid,d.departname, a.cailiaoNo,b.cailiaoName,gys.gongyingshangName, SUM(cast(a.jingzhongT as FLOAT))
from WZYCLJinChangGB as a, WZCaiLiaoNameDict as b, WZDibangxinxi as c, t_s_depart as d,WZGongYingShang as gys
where a.cailiaoNo=b.cailiaoNo and a.gongyingshangdanweibianma = gys.guid and a.shebeibianhao = c.gprsbianhao and c.departid=d.ID 
{} 
and a.shebeibianhao in ('{}')
{}
group by c.departid,d.departname, a.cailiaoNo,b.cailiaoName,gys.gongyingshangName
order by d.departname,gys.gongyingshangName
"""

CAILIAONO_SQL = """select cailiaoNo from WZCaiLiaoNameDict where parentNode in (select cailiaoNo from WZCaiLiaoNameDict where cailiaoName in (\'{}\'))"""

TEST_SQL = """select * from t_s_depart where ID in (\'{}\')"""

DEP_SQL = """WITH  dept
    AS (SELECT  *
        FROM   t_s_depart
        WHERE  parentdepartid = '{}'
        UNION ALL
        SELECT  d.*
        FROM   t_s_depart d
            INNER JOIN dept ON d.parentdepartid = dept.id
						 )
  SELECT id
  FROM  dept"""

PAR_ID = """WITH PAR AS
(
SELECT * FROM t_s_depart WHERE departname = '{}'
UNION ALL
SELECT T1.* FROM PAR,t_s_depart as T1 WHERE PAR.parentdepartid=T1.ID
)
SELECT * FROM PAR where departname not like '%监理%'"""

def supplierStatic(Args,user):
    print('Supplier参数:\n\t', Args)
    depId = Args['depId']
    # depId = 'f6323b406d19fc87016d1a5b43910012'
    dates = Args['dates']
    materials = Args['materials']
    testdepId = '\',\''.join(depId.split(','))
    tests = sqlquery(TEST_SQL.format(testdepId))
    print('TESTS:\n\t', tests.__len__())
    if tests.__len__() == 0:
        depId = 'All'
    if depId == 'All':
        deps = sqlquery(DEP_SQL.format(user))
        print('DEPSQL:\n\t', DEP_SQL.format(user))
        print(deps)
        depsid = []
        for dep in deps:
            depsid.append(dep[0])
        depId = '\',\''.join(depsid)
    else:
        depIds = depId.split(',')
        for did in depIds:
            deps = sqlquery(DEP_SQL.format(did))
            print('DEPSQL:\n\t', DEP_SQL.format(did))
            print(deps)
            depsid = []
            depsid.append(did)
            for dep in deps:
                depsid.append(dep[0])
        depId = '\',\''.join(depsid)
    dibangSql = DIBANG_SQL.format(depId)
    print('SQL:\n\t', dibangSql)
    dibangData = sqlquery(DIBANG_SQL.format(depId))
    dibangNo = []
    for dibang in dibangData:
        dibangNo.append(dibang[3])
    print('Data:\n\t', dibangNo)
    # query jinchang
    materials = '\',\''.join(materials.split(','))
    print(materials)
    print('SQL:\n\t', CAILIAONO_SQL.format(materials))
    cl = sqlquery(CAILIAONO_SQL.format(materials))
    print(cl)
    if cl.__len__() > 0:
        cls = []
        for c in cl:
            cls.append(c[0])
        materials = '\',\''.join(cls)
        clWhere = 'and a.cailiaoNo in (\'{}\') '.format(materials)
    else:
        clWhere = 'and cailiaoName like \'%{}%\' '.format(materials) if materials != 'All' else ''
    # clWhere = 'and cailiaoName like \'%{}%\' '.format(materials) if materials!='All' else ''
    dateWhere = 'and CONVERT(nvarchar(10), jinchangshijian, 120)in (\'{}\')'
    datearray = dates.split(',')
    print(datearray)
    marktitle = ''
    datemark = ''
    # markdown += '{}：\n\n'.format('供货商供货情况如下')
    if len(datearray) == 2:
        dateWhere = 'and CONVERT(nvarchar(10), jinchangshijian, 120)>=\'{}\' and CONVERT(nvarchar(10), jinchangshijian, 120)<=\'{}\''.format(
            datearray[0], datearray[1])
    else:
        dateWhere = dateWhere.format('\',\''.join(dates.split(',')))
    # dates = '\',\''.join(dates.split(','))
    dibangNo = '\',\''.join(dibangNo)
    cailiaoSql = CAILIAO_SQL.format(clWhere, dibangNo, dateWhere)
    print('CL_SQL:\n\t', cailiaoSql)
    cailiaoData = sqlquery(cailiaoSql)
    clData = {}
    resdata = []
    if cailiaoData.__len__() == 0:
        marktitle = '当前时间没有数据'
        return clData,resdata, marktitle
    else:
        if len(datearray) == 2:
            start = datetime.datetime.strptime(datearray[0], '%Y-%m-%d')
            end = datetime.datetime.strptime(datearray[1], '%Y-%m-%d')
            datemark += str(start.year) + '年' + str(start.month) + '月' + str(start.day) + '日' + '到' + str(
                end.year) + '年' + str(end.month) + '月' + str(end.day) + '日'
        else:
            start = datetime.datetime.strptime(datearray[0], '%Y-%m-%d')
            datemark += str(start.year) + '年' + str(start.month) + '月' + str(start.day) + '日'
    sup = ''
    marktitle = datemark + '的供货商供货如下'
    lot = {}
    site = {}
    title = {'key': 0, 'cailiao': '', 'zhongliang': ''}
    keyindex = 0
    for cailiao in cailiaoData:
        if os.environ.get("ENV_NAME") == '1':
            depName, clName,supplier, weight = cailiao[1], cailiao[3],cailiao[4], round(cailiao[5], 2)
        else:
            depName, clName,supplier, weight = cailiao[1], cailiao[3].encode('latin1').decode('gbk'),cailiao[4].encode('latin1').decode('gbk'), round(cailiao[5], 2)
        if depName not in clData:
            clData[depName] = {"materials": [], 'weights': [],'supplier':[]}
            sup = ''
            keyindex = 0
            if len(lot) == 0:
                lot = {'title': depName, 'children': []}
            else:
                if len(lot) != 0:
                    resdata.append(lot)
                lot = {'title': depName, 'children': []}
        if weight > 0:
            clData[depName]['materials'].append(clName)
            clData[depName]['weights'].append(weight)
            clData[depName]['supplier'].append(supplier)
            if sup != supplier:
                sup = supplier
                keyindex = 0
                if len(site) == 0:
                    site = {'title': supplier, 'data': {
                        'tableHead': [{
                            'title': '材料',
                            'dataIndex': 'cailiao',
                            'key': 'cailiao',
                        },
                            {
                                'title': '总重量',
                                'dataIndex': 'zhongliang',
                                'key': 'zhongliang',
                            }],
                        'tableBody': []
                    }}
                else:
                    lot['children'].append(site)
                    site = {'title': supplier, 'data': {
                        'tableHead': [{
                            'title': '材料',
                            'dataIndex': 'cailiao',
                            'key': 'cailiao',
                        },
                            {
                                'title': '总重量',
                                'dataIndex': 'zhongliang',
                                'key': 'zhongliang',
                            }],
                        'tableBody': []
                    }}
            keyindex = keyindex + 1
            title['key'] = keyindex
            title['cailiao'] = clName
            title['zhongliang'] = weight
            site['data']['tableBody'].append(title)
            title = {'key': 0, 'cailiao': '', 'zhongliang': ''}
    if len(title) != 0:
        site['data']['tableBody'].append(title)
    if len(site) != 0:
        lot['children'].append(site)
    if len(lot) != 0:
        resdata.append(lot)
    return clData, resdata, marktitle



functionConf = {
    "name": "supplierStatic",
    "description": "Query material entry weighing data and associated suppliers based on outbound ID, time, and material name",
    "parameters": {
        "type": "object",
        "properties": {
            "depId": {
                "type": "string",
                "description": "Department ID, multiple departments separated by commas in English, default to All if no department is specified, default to All",
            },
            "dates": {
                "type": "string",
                "description": "The time of the material, using yyyy-MM-dd format, with multiple dates separated by commas, for example:2023-07-14,2023-07-15"
            },
            "materials": {
                "type": "string",
                "description": "Item names, multiple items separated by commas, all items using 'All', such as fly ash, cement"
            }
        },
        "required": [
            "depId",
            "dates",
            "materials"
        ]
    }
}